const models = require('../db/mysqldb/index')
const { Op } = require("sequelize");

class DataCSVModel {
  /**
     * @description 根据path查找,不包含删除数据
     * @param {string} data_api_id 
     * @param {string} name 
     * @returns 
     */
  static async findByPath (path) {
    return await models.data_csv.findOne({
      where: {
        path,
        delete_timestamp: {
          [Op.is]: null,
        }
      }
    })
  }
  /**
  * @description 通过id查找,不包含删除数据
  * @param {string} data_csv_id 
  * @returns 
  */
  static async findById (data_csv_id,) {
    return await models.data_csv.findOne({
      where: {
        delete_timestamp: {
          [Op.is]: null,
        },
        data_csv_id
      }
    })
  }
  /**
  * @description 通过path和id查找数据，不包含删除数据
  * @param {string} path 
  * @param {string} data_csv_id 
  * @returns 
  */
  static async findByPathAndId (path, data_csv_id,) {
    return await models.data_csv.findOne({
      where: {
        path,
        delete_timestamp: {
          [Op.is]: null,
        },
        data_csv_id: {
          [Op.ne]: data_csv_id,
        },
      }
    })
  }
  /**
  * @description 通过path和id查找数据，不包含删除数据
  * @param {string} data_csv_id 
  * @returns 
  */
  static async findAllById (data_csv_id,) {
    return await models.data_csv.findOne({
      where: {
        data_csv_id
      }
    })
  }
  /**
   * @description create
   * @param {string} name 
   * @param {string} path 
   * @param {string} remark 
   * @param {string} op_user_id 
   * @returns 
   */
  static async create (name, path, remark, op_user_id,) {
    return await models.data_csv.create({
      name, path, remark,
      create_by: op_user_id
    })
  }
  /**
   * @description update
   * @param {string} path 
   * @param {string} data_csv_id 
   * @returns 
   */
  static async update (name, path, remark, op_user_id, data_csv_id) {
    return await models.data_csv.update(
      {
        name, path, remark,
        update_by: op_user_id,
        update_timestamp: Date.now()
      },
      {
        where: { data_csv_id },
      }
    )
  }
  /**
    * @description delete
    * @param {string} op_user_id 
    * @param {string} data_csv_id 
    * @returns 
    */
  static async delete (op_user_id, data_csv_id) {
    return await models.data_csv.update(
      {
        delete_timestamp: Date.now(),
        delete_by: op_user_id
      },
      {
        where: { data_csv_id },
      }
    )
  }
  /**
   * @description list
   * @param {*} limit 
   * @param {*} size 
   * @param {*} name 
   * @param {*} remark 
   * @returns 
   */
  static async list (size = 1,limit, name, remark,) {
    let sql = `select SQL_CALC_FOUND_ROWS data_csv_id,name,remark, path,update_by,update_timestamp,create_by,create_timestamp from data_csv where 1 = 1 AND delete_timestamp IS NULL AND delete_timestamp IS NULL `
    if (name) {
      sql = sql + `AND name like '%${name}%' `
    }
    if (remark) {
      sql = sql + `AND remark like '%${remark}%' `
    }
    sql = sql + "ORDER BY create_timestamp DESC ,update_timestamp DESC "
    if (limit) {
      sql = sql + `LIMIT ${limit} OFFSET  ${(size - 1) * limit} `
    }
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    const count = await models.sequelize.query(`SELECT FOUND_ROWS() as count;`, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return { result, count }
  }
  /**
  * @description allList
  * @returns 
  */
  static async allList () {
    let sql = `select data_csv_id as value,name as label from data_csv where 1 = 1 AND delete_timestamp IS NULL AND delete_timestamp IS NULL `
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return result
  }
}
module.exports = {
  DataCSVModel,
}
